package main

import (
	"database/sql"
	"fmt"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db := getDb()

	//创建表
	sql_table := `
    CREATE TABLE IF NOT EXISTS userinfo(
        uid INTEGER PRIMARY KEY AUTOINCREMENT,
        username VARCHAR(64) NOT NULL DEFAULT '',
        departname VARCHAR(64)  NOT NULL DEFAULT '',
        created DATE NULL
    );
    `
	db.Exec(sql_table)

	stmt, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)")
	checkErr(err)
	res, err := stmt.Exec("郑洪耕", "福州", "2021-12-30")
	checkErr(err)
	id, err := res.LastInsertId()
	fmt.Printf("Insert id: %d\n", id)

	rows, err := db.Query("SELECT * FROM userinfo")
	defer rows.Close()
	checkErr(err)
	var uid int
	var username string
	var department string
	var created time.Time

	for rows.Next() {
		err = rows.Scan(&uid, &username, &department, &created)
		checkErr(err)
		fmt.Println(uid)
		fmt.Println(username)
		fmt.Println(department)
		fmt.Println(created)
	}

	rows.Close()
	delete(1)
	defer db.Close()
}

func delete(id int64) {
	db := getDb()

	stmt, err := db.Prepare("delete from userinfo where uid=?")
	checkErr(err)

	res, err := stmt.Exec(id)
	checkErr(err)

	affect, err := res.RowsAffected()
	checkErr(err)
	fmt.Printf("Delete row count: %d\n", affect)
}

func getDb() *sql.DB {
	db, err := sql.Open("sqlite3", "./uid.db")
	checkErr(err)
	return db
}

func checkErr(err error) {
	if err != nil {
		panic(err)
	}
}
